﻿using System;
using System.IO;
using Kivensoft.XlsWriter;

namespace Kivensoft.Helper {
	/*
		var headers = new string[] { "姓名", "年龄", "电话", "家庭住址", "余额" };
		var widths = new float[] {14f, 12f, 20f, 22f, 10f};

		var xls = new XlsxExport("工作表33");
		xls.SetTitle("大标题", new string[] {"查询日期", "打印日期"},
			new int[] {3, 2}, headers, widths);
		
		xls.GoToNextRow();
		xls.AddNextCell("Kiven", true);
		xls.AddNextCell(42);
		xls.AddNextCellDateTime(DateTime.Now);
		xls.AddNextCellDate(DateTime.Today);
		xls.AddNextCellMoney(3459.8);

		xls.GoToNextRow();
		xls.AddNextCell("Kiven2", true);
		xls.AddNextCell(76);
		xls.AddNextCellDateTime(DateTime.Now);
		xls.AddNextCellDate(DateTime.Today);
		xls.AddNextCellMoney(4421);

		xls.SetFooter("合计", 3, new bool[] {false, true});
		
		var f = File.Open(@"z:\测试.xlsx", FileMode.Create);
		xls.Write(f);
		f.Close();
	*/
	public class XlsxExport {
		private Workbook workbook;
		private Worksheet sheet;
		private int currentCol, currentRow;
		private bool toggleFlag = true;
		private Style titleStyle;
		private Style[] subTitleStyles;
		private Style headerStyle;
		private Style[] recordStyles;
		private Style footerStyle;

		public Workbook Workbook { get { return workbook; } }
		public Worksheet Sheet { get { return sheet; } }

		public XlsxExport(string sheetName) {
			workbook = new Workbook(sheetName);
			sheet = workbook.CurrentWorksheet;

			//创建边框样式
			var b = new Style.Border();
			b.TopStyle = Style.Border.StyleValue.thin;
			b.BottomStyle = b.TopStyle;
			b.LeftStyle = b.TopStyle;
			b.RightStyle = b.TopStyle;

			//创建日期格式化样式
			var fmt = new Style.NumberFormat();
			fmt.Number = Style.NumberFormat.FormatNumber.custom;
			fmt.CustomFormatID = 250;
			fmt.CustomFormatCode = "yyyy-mm-dd hh:mm:ss";

			titleStyle = new Style("title");
			titleStyle.CurrentFont.Bold = true;
			titleStyle.CurrentFont.Size = 24;
			titleStyle.CurrentCellXf.HorizontalAlign = Style.CellXf.HorizontalAlignValue.center;

			subTitleStyles = new Style[2];
			subTitleStyles[0] = new Style("subTitleLeft");
			subTitleStyles[1] = new Style("subTitleRight");
			subTitleStyles[1].CurrentCellXf.HorizontalAlign = Style.CellXf.HorizontalAlignValue.right;

			headerStyle = new Style("header");
			headerStyle.CurrentBorder = b;
			headerStyle.CurrentFont.Bold = true;
			headerStyle.CurrentFill.SetColor("00DCDCDC", Style.Fill.FillType.fillColor);
			headerStyle.CurrentCellXf.HorizontalAlign = Style.CellXf.HorizontalAlignValue.center;

			int rlen = 10, rharf = rlen >> 1;
			recordStyles = new Style[rlen];
			for (int i = 0; i < rlen; i++) {
				var rs = new Style("record_" + i);
				rs.CurrentBorder = b;
				if (i >= rharf) rs.CurrentFill.SetColor("00F2F2F2", Style.Fill.FillType.fillColor);
				recordStyles[i] = rs;
			}
			recordStyles[1].CurrentCellXf.HorizontalAlign = Style.CellXf.HorizontalAlignValue.center;
			recordStyles[1 + rharf].CurrentCellXf = recordStyles[1].CurrentCellXf;
			recordStyles[2].CurrentNumberFormat.Number = Style.NumberFormat.FormatNumber.format_4;
			recordStyles[2 + rharf].CurrentNumberFormat = recordStyles[2].CurrentNumberFormat;
			recordStyles[3].CurrentNumberFormat = fmt;
			recordStyles[3 + rharf].CurrentNumberFormat= recordStyles[3].CurrentNumberFormat;
			recordStyles[4].CurrentNumberFormat.Number = Style.NumberFormat.FormatNumber.format_14;
			recordStyles[4 + rharf].CurrentNumberFormat = recordStyles[4].CurrentNumberFormat;

			footerStyle = new Style("footer");
			footerStyle.CurrentBorder = b;
			footerStyle.CurrentFont.Bold = true;
			footerStyle.CurrentFill.SetColor("00DCDCDC", Style.Fill.FillType.fillColor);
		}

		public void SetTitle(string title, string[] subTitles, int[] subTitleMerges,
				string[] headers, float[] widths) {

			if (string.IsNullOrEmpty(title) || headers.Length != widths.Length)
				throw new Exception("设置标题校验错误！");

			var hlen = headers.Length;

			//设置一级标题
			var c = new Cell(title, Cell.CellType.DEFAULT, 0, 0);
			c.SetStyle(titleStyle, workbook);
			sheet.AddCell(c);
			sheet.MergeCells(new Cell.Address(0, 0), new Cell.Address(hlen - 1, 0));
			
			//设置二级子标题
			if (subTitles != null) {
				int next_col = 0;
				for (int i = 0; i < subTitles.Length; i++) {
					c = new Cell(subTitles[i], Cell.CellType.DEFAULT, next_col, 1);
					c.SetStyle(subTitleStyles[i], workbook);
					sheet.AddCell(c);
					var cm = subTitleMerges[i];
					if (cm > 1)
					sheet.MergeCells(new Cell.Address(next_col, 1), new Cell.Address(next_col + cm - 1, 1));
					next_col += cm;
				}
				currentRow = 1;
			}
			
			//设置列头
			currentRow++;
			for (int i = 0; i < hlen; i++) {
				c = new Cell(headers[i], Cell.CellType.DEFAULT, i, currentRow);
				c.SetStyle(headerStyle, workbook);
				sheet.AddCell(c);
				sheet.SetColumnWidth(i, widths[i]);
			}

		}

		/// <summary>
		/// 转到下一行，设置完标题后，在写入每行记录之前调用
		/// </summary>
		public void GoToNextRow() {
			currentRow++;
			currentCol = 0;
			toggleFlag = !toggleFlag;
		}

		/// <summary>
		/// 在当前行当前列新增一个单元格，新增完成后当前列会自动加1
		/// </summary>
		/// <param name="value">单元格的值</param>
		/// <param name="isCenter">是否居中显示</param>
		public void AddNextCell(object value, bool isCenter = false) {
			Cell c = new Cell(value, Cell.CellType.DEFAULT, currentCol++, currentRow);
			var inc = !toggleFlag ? 0 : 5;
			if (isCenter) c.SetStyle(recordStyles[1 + inc], workbook);
			else c.SetStyle(recordStyles[0 + inc], workbook);
			sheet.AddCell(c);
		}

		public void AddNextCellMoney(object value) {
			Cell c = new Cell(value, Cell.CellType.DEFAULT, currentCol++, currentRow);
			var inc = !toggleFlag ? 0 : 5;
			c.SetStyle(recordStyles[2 + inc], workbook);
			sheet.AddCell(c);
		}

		public void AddNextCellDateTime(DateTime? value) {
			Cell c = new Cell(value, Cell.CellType.DATE, currentCol++, currentRow);
			var inc = !toggleFlag ? 0 : 5;
			c.SetStyle(recordStyles[3 + inc], workbook);
			sheet.AddCell(c);
		}

		public void AddNextCellDate(DateTime? value) {
			Cell c = new Cell(value, Cell.CellType.DATE, currentCol++, currentRow);
			var inc = !toggleFlag ? 0 : 5;
			c.SetStyle(recordStyles[4 + inc], workbook);
			sheet.AddCell(c);
		}

		/// <summary>
		/// 设置表尾的合计列
		/// </summary>
		/// <param name="title">合计列标题，位于该行首列</param>
		/// <param name="titleMerge">合计列标题的合并单元格数量</param>
		/// <param name="footers">剩余单元格的是否计算合计值数组，true为计算，false忽略</param>
		public void SetFooter(string title, int titleMerge, bool[] footers) {
			GoToNextRow();
			if (!string.IsNullOrEmpty(title)) {
				Cell c = new Cell(title, Cell.CellType.DEFAULT, 0, currentRow);
				c.SetStyle(footerStyle, workbook);
				sheet.AddCell(c);
				if (titleMerge > 1) {
					for (int i = 1; i < titleMerge; i++) {
						c = new Cell(string.Empty, Cell.CellType.EMPTY, i, currentRow);
						c.SetStyle(footerStyle, workbook);
						sheet.AddCell(c);
					}
					sheet.MergeCells(new Cell.Address(0, currentRow),
						new Cell.Address(titleMerge - 1, currentRow));
				}
				currentCol = titleMerge;
			}
			for (int i = 0; i < footers.Length; i++) {
				var b = footers[i];
				Cell c;
				if (b) {
					var addr1 = new Cell.Address(currentCol + i, 3);
					var addr2 = new Cell.Address(currentCol + i, currentRow - 1);
					c = new Cell(string.Format("SUM({0}:{1})",
						addr1.GetAddress(), addr2.GetAddress()),
						Cell.CellType.FORMULA, currentCol + i, currentRow);
				}
				else c = new Cell(string.Empty, Cell.CellType.EMPTY, currentCol + i, currentRow);
				c.SetStyle(footerStyle, workbook);
				sheet.AddCell(c);
			}
		}

		public void Write(Stream stream) {
			workbook.Write(stream);
		}

		public void Save(string fileName) {
			workbook.SaveAs(fileName);
		}
	}
}
